Як правильно встановлювати зв'язки між даними?

Виконай дії, проаналізуй результати та зроби висновки.*

1. У клітинку А1 введи число 1.

2. У клітинку А2 введи число 2.

3. Виділи клітинки А1 та А2 та протягни маркер заповнення

до клітинки А10.

4. У клітинку В1 введи формулу =А1+1.

5. Виділи клітинку В1 та протягни маркер заповнення

до клітинки J1.

6. Ознайомся із результатами та вилучи дані із діапазону С1: J1.

7. Відредагуй формулу у клітинці В1, записавши її у вигляді =$А1+1.

8. Виділи клітинку В1 та протягни маркер заповнення

До

клітинки J1.

9. Виділи клітинку В1 та протягни маркер заповнення

До

клітинки В10.

10. Перейди на вкладку Формули і увімкни режим «Показати формули». Пункт меню «Вигляд» — «Показати» — «Формули». Ознайомся із результатами.

Дай відповіді на запитання:

Чому формули =A1+1 та =$A1+1 під час копіювання дають різні результати? Що означає символ $ у формулах?

Завдання № 2

Створи таблицю у відповідності до зразка. Додай формули, яких не вистачає. Усі формули, окрім загальної суми, повинні вводитися тільки один раз і далі копіюватися за допомогою маркера заповнення.

Добери кількість обладнання за зазначеними цінами і курсом долара так, щоб загальна сума дорівнювала приблизно 1000 грн, але не перевищувала цю величину. Порівняй свої результати з результатами однокласників. Чи однакові у вас рішення?

Зміни кількість обладнання, якщо умови закупівлі ті самі, а курс долара підвищився на 10 %.

Розглянь подане вікно, дай відповіді на запитання.

Які категорії функцій є в табличному процесорі?

Чим вони відрізняються?

Як дізнатися правила введення аргументів обраної функції?

Яку інформацію містить довідка до формули?

Розглянь життєві задачі. Які матема-тичні/статистичні або логічні функції в Ехсе! допомогли б їх вирішити?

А) Менеджеру в супермаркеті необхідно обчислити мінімальну, максимальну і середню вартість покупки.

Б) Фірма отримала дані про те, що попит на планшети ціною вище 100$ недостатній, і вирішила зробити знижку 10 % на такі моделі. Нам потрібно обчислити знижку і ціни з урахуванням знижки.

http://bit.do/fSTZi

Дізнайся про застосування цих функцій у розділі «Запитання-від-повіді» та розв'яжи ці задачі в електронній таблиці, скориставшись файлом-заготовкою Функції.х^х. Вхідні дані для різних задач розміщено на різних аркушах цього файлу.

Завдання № 5

Тепер припустимо, що менеджеру мережі магазинів, який слідкує за ціновою політикою, потрібно, щоб ціни менше 2000 грн виділялися жовтою заливкою. Дізнайся з рубрики «Запитання-відповіді», як розв'язати цю задачу автоматизовано, і зроби це у файлі від попереднього завдання.

Завдання № 6

Об'єднайтесь у пари, розв'яжіть завдання.

У клітинку А1 введено число 5, а у клітинку А2 — число 9. У клітинці А3 відображається число 7. Які формули можуть бути введені в клітинку А3?

Завдання № 7

Об'єднайтесь у пари, розв'яжіть завдання спочатку усно, а потім — в електронній таблиці.

1) Банк надає депозити для населення під 21 % річних. Інформація про розмір вкладу клієнта з 2016 по 2020 рр. відображена в таблиці.

Яка формула може бути введена в клітинку С3?

ЗАПИТАННЯ-ВІДПОВІДІ

Що таке посилання на клітинку?

Посилання на клітинку — це вказівка на адресу клітинки у формулі. Наприклад:

(формула з посиланням на клітинку з адресою А2);

(формула, що містить посилання на клітинки з адресами А2 та в2).

Що таке абсолютні та відносні посилання в Excel?

Відносні посилання — це посилання, які при копіюванні формул змінюються відповідним чином (адреса аргументу «зсувається» на ту саму кількість клітинок по горизонталі і вертикалі, на яку переміщено формулу).

Наприклад, якщо до клітинки С3 записали формулу

і скопіювали цю формулу до клітинок D3, E3, F3, G3, то можна побачити, як із кожним переміщенням змінюється формула.

Абсолютні посилання — це посилання, які при копіюванні формул не змінюються. Такі посилання використовують, коли зміна адреси у посиланнях на клітинку небажана. Абсолютні посилання відрізняються від відносних наявністю знаку $ перед назвою стовпця і номером рядка.

Розглянь, наприклад, результат таких дій: формула

введена в клітинку С3, була скопійована спочатку в діапазон D3:G3, потім — у C4:G4.

Мішані посилання — це посилання, в яких під час копіювання формул не змінюється лише назва стовпця або номер рядка. У мішаних посиланнях знак $ використовують або безпосередньо перед назвою стовпця, або перед номером рядка, який потрібно залишити без змін.

Приклад копіювання формул:

для створення абсолютних посилань також можна надавати ім'я клітинці.

Правила складання імен: в імені не можна використовувати пропуски; ім'я не може розпочинатися із цифри; ім'я не може збігатися з адресами клітинок.

Імена клітинок не можна повторювати в одній таблиці. Для перевірки списку імен, які вже використанні, потрібно натиснути F3.

Як у табличному процесорі вводити формули?

За допомогою табличного процесора можна виконувати різноманітні обчислення в електронних таблицях. Усі обчислення виконуються згідно з формулами, які вводить користувач. У формулах можна використовувати числові значення, адреси клітинок, вбудовані функції і знаки арифметичних операцій: +, -, *, /, ^Л, %.

Введення формули починається зі знака «=», після якого вводиться сама формула, а завершується натисканням клавіші «Enter». Після цього в клітинці з'являється результат або повідомлення про помилку, якщо формула введена неправильно.

Редагувати формулу можна в самій клітинці або в рядку формул.

Що таке функції в табличному процесорі та як їх використовувати?

Як і в математиці, у табличному процесорі функція виконує певну обчислювальну задачу. Функція завжди є частиною якоїсь формули. вона має ім'я і список аргументів у круглих дужках. Аргументами можуть бути числові та текстові константи, клітинки, діапазони клітинок.

ввести функції у формулу можна вручну або з використанням майстра функцій. для роботи з майстром функцій (див. приклад вікна на стор. 90 «вставлення функції») слід натиснути кнопку «Майстер функцій»* зліва від рядка формул. При цьому відкривається діалогове вікно «вставлення функції», в якому можна вибрати категорію функцій (статистичні, математичні, фінансові), а нижче — саму функцію. Потім слід натиснути кнопку «OK» і в діалоговому вікні «Аргументи функції» вказати аргументи функції. Після натискання кнопки «Готово» формула вставляється в активну клітинку.

У Google таблицях можна увімкнути режим підказок.

*Для таблиць Excel

У новіших версіях Excel можна додати функцію, скориставшись вкладкою «Формули».

Як правильно вводити функції для обчислення суми найбільшого, найменшого та середнього значення, кількості клітинок із даними, найбільший та найменший аргумент у списку?

Прості математичні та статистичні формули

SUM

SUM(Number1; Number2;) — повертає суму числових аргументів.

Ім'я аргументу

опис аргументів функції

Number1

(обов'язковий)

Перший аргумент може бути константою (8), посиланням на клітинку (А3) або на діапазон клітинок (B2:B8).

Number2-255

(необов'язковий)

другий аргумент може містити такі самі значення: константа, посилання на клітинку або діапазон.

Можна вказати до 255 аргументів.

AVERAGE

AVERAGE(Number1; Number2;) — повертає середнє арифметичне значення аргументів, які можуть бути числами, іменами або посиланнями на клітинки з числами. ім'я аргументу опис аргументів функції Number1 (обов'язковий)

Перший аргумент може бути константою (8), посиланням на клітинку (А3) або на діапазон клітинок (B2:B8).

Number2-255

(необов'язковий)

додаткові аргументи, для яких потрібно обчислити середнє значення. Можна вказати до 255 аргументів.

COUNT

COUNT(Value1; Value2;) — повертає кількість клітинок діапазону,

в яких містяться числа.

ім'я аргументу

опис аргументів функції

Value1

(обов'язковий)

Посилання на окремі клітинки або діапазон клітинок (B2:B8), у якому потрібно обчислити кількість клітинок, які містять числа. Value2-255 (необов'язковий)

додаткові посилання на клітинки та діапазони, можна вказати до 255 посилань.

Увага! враховуються лише аргументи, які є числами, датами або текстовим поданням чисел.

Аргументи, які є значеннями помилок або текстом, який не можна перетворити в числа, пропускаються.

MAX

MAX(Number1; Number2;) — повертає найбільше значення з набору значень.

Ім'я аргументу

Опис аргументів функції

Number1

(обов'язковий)

Перший аргумент може бути константою (8), посиланням на клітинку (А3) або на діапазон клітинок (B2:B8).

Number2-255

(необов'язковий)

додаткові посилання на клітинки та діапазони, можна вказати до 255 посилань.

MIN

MIN(Number1; Number2;) — повертає найменше значення з набору значень.

Ім'я аргументу

опис аргументів функції

Number1

(обов'язковий)

Перший аргумент може бути константою (8), посиланням на клітинку (А3) або на діапазон клітинок (B2:B8).

Number2-255

(необов'язковий)

додаткові посилання на клітинки та діапазони, можна вказати до 255 посилань.

Які бувають помилки при введенні формул?

##### — числова величина не вміщається в клітинці;

#DIV/0! — спроба ділення на нуль;

#NAME? — неможливо розпізнати, що використано у формулі. Наприклад, використано неповну адресу клітинки;

#VALUE — формула повертає величину, що не відповідає значенню формули або функції. Ця помилка також виникає у випадку посилання на текст замість числа;

#REF! — використано недопустиме посилання на клітинку.

Крім того, помилкою є циклічні посилання, тобто посилання у формулі на клітинку, яка використовується для обчислення самої цієї формули. Призводить до повідомлення про помилку («Виявлене циклічне посилання»). Дані в клітинці з такими посиланнями обну-ляються. Роботу формули заблоковано, тобто при зміні даних перерахунку величини функції проведено не буде.

для розв'язання яких задач використовують логічну функцію IF?

Логічну функцію IF використовують для перевірки умов. Зіставляються числа і/або текст, функції, формули тощо. Коли значення відповідають заданим параметрам, обчислюється один вираз. Не відповідають — інший.

Синтаксис функції:

(логічний_вираз; значення_якщо умова істинна; значення_якщо умова хибна).

логічний_вираз — ЩО саме перевіряється (умова). Значення_якщо умова істинна — ЩО з'явиться у клітинці, якщо значення відповідають заданій умові (умова істинна). Значення_якщо умова хибна — ЩО з'явиться у клітинці, якщо значення НЕ відповідають заданій умові (умова хибна).

Задача:

Визначити виконання нормативу по стрибках у довжину (нормою є довжина стрибка більше 165 сантиметрів). Приклад введення формули за допомогою Майстра функцій Microsoft Excel.

Результат

Приклад введення логічної формули IF у Google таблицях:

Задача:

У створену таблицю автоматично внести показник нормативу по стрибках у довжину. Норматив для дівчат — потрібно стрибнути більше ніж на 160 самнтиметрів, а для хлопці більше ніж на 165 сантиметрів.

Результат

ФУНКЦІЯ IF із декількома умовами

Часто на практиці однієї умови для логічної функції мало. Коли потрібно врахувати кілька варіантів прийняття рішень, можна вкладати оператори ЯКЩО один в одного.

Потрібно визначити агрегатний стан води залежно від її температури.

Запис формули:

Як перевірити складені умови?

Якщо умова формулюється з використанням сполучників «і» чи «або», для її запису використовують функції AND (і) чи OR (або).

Приклад використання функції AND.

Результат

Задача:

Визначити, чи є серед трьох чисел два однакових числа?

Приклад використання функції OR.

Запис формули:

Що таке умовне форматування?

За допомогою умовного форматування можна наочно досліджувати й аналізувати дані, виявляти критичні проблеми, визначати закономірності й тенденції.

воно дає можливість форматувати клітинки автоматично на підставі їхніх значень. Наприклад, ви можете задати умовне форматування таким чином, щоб усі клітинки з від'ємними значеннями зафарбовувати в червоний колір. Коли ви вводите або міняєте значення в клітинці, табличний процесор перевіряє його і порівнює з умовами, заданими в правилах. Якщо умови виконуються (значення від'ємне), клітинки зафарбуються, якщо ні — залишаться без змін.

Приклад 1

Зображені дані виділені кольорами за принципом: заливка з темно-жовтим текстом для клітинок, що містять значення 95; зелена заливка з темно-зеленим текстом для клітинок зі значеннями більше 95; світло-червона заливка з темно-червоним текстом

для клітинок, що містять дані менше 95.

Приклад 2

Умовне форматування позначками (автоматично відбувається аналіз даних та ставиться відповідна умовна позначка).

Також умовне форматування — це простий спосіб визначити клітинки з помилковими записами або значеннями певного типу.

Які є види умовного форматування?

Усі сценарії умовного форматування поділені на такі категорії: Правило виділення клітинок.

Правило відбору перших і останніх значень.

Гістограми.

Кольорові шкали.

Набори піктограм.

Правила виділення клітинок застосовують до тих клітинок, які порівнюються з певним значенням.

Більше... Якщо значення клітинки, до якої застосовується правило виділення, більше заданої величини, то починає діяти зазначений формат.

менше... Форматує клітинки, у яких значення менше заданого пор^огу.

між... Форматування застосовується, якщо вміст клітинки знаходиться в заданих межах.

дорівнює... Якщо значення або текст в клітинці збігається з умовою.

Текст, який містить... Якщо збігається тільки частина тексту (слово, код, комбінація символів та ін.).

Дата... Можливість форматувати періоди часу наприклад, сьогодні, вчора, останні 7 днів, наступний місяць та ін.

Повторювані значення... виділяються клітинки з однаковим вмістом. відмінний спосіб знайти дублікати (повтори). У налаштуваннях можна вибрати і зворотний варіант — виділити тільки унікальні значення.

Правила відбору перших і останніх значень виділяють найбільші або найменші значення. допомагають аналізувати дані, показуючи пріоритети і «слабкі місця».

10 перших елементів... Форматується певна кількість найбільших значень. Кількість вказують в діалоговому вікні (можна задати 5, 20 та ін.).

Перші 10 %... виділяються 10 % максимальних значень. Частку можна змінити.

10 останніх елементів... Форматується певна кількість найменших значень.

останні 10 %... Найменші 10 % або інша частка від усіх елементів. Більше середнього... Форматує всі значення, які більші за середнє арифметичне.

Менше середнього... Менше за середнє арифметичне. Гістограми дають можливість у кожну клітинку з числом додати стовпець лінійної гістограми, розмір якого визначається відносно максимального значення у виділеному діапазоні.

Це допомагає візуалізувати невеликий набір даних без використання окремих діаграм.

Кольорові шкали визначають максимальне і мінімальне значення в діапазоні і форматують кожну клітинку за кольором, який відповідає положенню значення між мінімальним та максимальним, зображуючи щось на зразок теплової карти.

Набори піктограм — кожній клітинці присвоюється позначка відповідно до обраного стилю.

ПЕРЕВІР СЕБЕ

1. Наведи приклади абсолютних, відносних та мішаних посилань.

2. Поясни різницю між абсолютними, відносними та мішаними посиланнями.

3. Наведи приклади відомих тобі математичних, статистичних та логічних функцій табличного процесора.

4.

Знайди значення функції у=2х+5, якщо значення х змінюється на відрізку [-10;10] з кроком 1.

5.

Завантаж файл

та

виконай завдання.

6.

Ознайомся з інформацією і створи подану послідовність чисел в електронній таблиці (вводити потрібно два перших числа і одну формулу, яку потім слід скопіювати):

http://bit.do/fSPUs

У своїй видатній «Книзі абака» (Liber abaci) леонардо Пізан-ський , більше відомий як Фібоначчі, наводить послідовність чисел, кожне з яких дорівнює сумі двох попередніх. Така послідовність отримала назву числа Фібоначчі.

0, 1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89, 144, 233, 377, 610, 987 і т. д.

7.

Склади таблицю відповідно до зразка, виконай необхідні обчислення та встанови умовне форматування умовними позначками для виставлених суддями балів.

Прізвище та ім'я	Оцінки суддів					Найвищий бал	Найнижчий бал	Сума балів
	1 суддя	2 суддя	3 суддя	4 суддя	5 суддя
Голуб Софія
Кожух Олексій
дерев'янко Павло
Мороз дарина
Зарудний Андрій
Сокол Андрій

Створи таблицю множення за зразком. Достатньо ввести одну формулу з мішаними посиланнями в клітинку B2, а потім скопіювати її в діапазон B2:K11. Подумай, яка це має бути формула.

9.

Розв'яжи подані задачі.

А) Менеджер із закупівель відстежує позиції, які потребують поповнення. для цього він дивиться в останню колонку, де розраховується товарний запас у тижнях. Якщо товарний запас менший трьох одиниць, потрібно готувати замовлення. Якщо менший двох — виникає ризик дефіциту і замовлення потрібно розміщувати терміново. Якщо в таблиці десятки позицій, на перегляд кожного рядка піде багато часу, тому потрібно встановити умовне форматування: значення нижче порогових підсвічуються кольором. Наприклад: жовтий — увага, закінчується; червоний — товар відсутній.

Товар

Середні продажі в шт./за тижд.

Залишок

Товарний запас на кількість тижнів

Б) Знайди значення функції для діапазону х від -10 до І0.

 

 

 

Це матеріал з підручника Інформатика 9 клас Коршунова 2022